package cn.hpclub.server.service;

import cn.hpclub.server.model.CmccCity;
import cn.hpclub.server.model.Organization;
import cn.hpclub.server.util.DateKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by wanglin on 2017/3/8.
 */
public class DzgDailyService {
    private static final Logger logger = LoggerFactory.getLogger(DzgDailyService.class);
    public static DzgDailyService service = new DzgDailyService();
    private static String START_TIME = "startTime";
    private static String END_TIME = "endTime";

    public List<Record> listMonthly(String date) {
        Map<Integer, Long> activeCountMap = getActiveCount(date);
        Map<String, Record> monthCountMap = getMothlyCountInfo(date);
        List<Record> result = new ArrayList<>();

        if(activeCountMap.size()==0&&monthCountMap.size()==0) return result;
        List<Record> cityInfos = getCityInfos();

        Record r = null;
        int t_activeNumber = 0;
        int t_srNumber = 0;
        int t_classNumber = 0;
        int t_booNumber = 0;
        int t_total = 0;
        for (Record cityInfo : cityInfos) {
            String city = cityInfo.getStr(Organization.DISPLAYNAME);
            int cityCode = cityInfo.getInt(Organization.CITYCODE);
            long activeNumber = activeCountMap.containsKey(cityCode)?activeCountMap.get(cityCode):0;//活跃人数
            long srNumber = 0;//宽带预约累计办理
            long classNumber =  0;//开放渠道累计办理
            long booNumber = 0;//宽带开户累计办理
            long total = 0;//业务总共办理量
            if(monthCountMap.get(city)!=null){
                srNumber = monthCountMap.get(city).getBigDecimal("srNumber").longValue();//宽带预约累计办理
                classNumber =  monthCountMap.get(city).getBigDecimal("classNumber").longValue();//开放渠道累计办理
                booNumber = monthCountMap.get(city).getBigDecimal("booNumber").longValue();//宽带开户累计办理
                total = monthCountMap.get(city).getBigDecimal("total").longValue();//业务总共办理量
            }
            t_activeNumber += activeNumber;
            t_srNumber += srNumber;
            t_classNumber += classNumber;
            t_booNumber += booNumber;
            t_total += total;
            r = new Record();
            r.set("city", city);
            r.set("activeNumber", activeNumber);
            r.set("srNumber", srNumber);
            r.set("classNumber", classNumber);
            r.set("booNumber", booNumber);
            r.set("total", total);
            result.add(r);
        }
        r = new Record();
        r.set("city", "合计");
        r.set("activeNumber", t_activeNumber);
        r.set("srNumber", t_srNumber);
        r.set("classNumber", t_classNumber);
        r.set("booNumber", t_booNumber);
        r.set("total", t_total);
        result.add(r);
        return result;
    }

    public List<Record> getCityInfos(){
        String sql = "SELECT o.CityCode,o.DisplayName FROM organization o where o.CityCode=o.id ";
        List<Record> list = Db.find(sql.toString());
        return list;
    }

    public Map<String, Record> getMothlyCountInfo(String date){
        StringBuffer sql = new StringBuffer("SELECT ddr.city,");
        sql.append(" sum(ddr.broadband_count) as booNumber,sum(open_website_count) as classNumber,sum(ddr.service_reservation_count) as srNumber,");
        sql.append(" sum(broadband_count + service_reservation_count + postage_count + traffic_count + marketing_activities_count + open_count + register_realname_count + good_business_count) AS total");
        sql.append(" FROM dzg_daily_report ddr WHERE date_format(ddr.count_date,'%Y-%m')='" + date + "' GROUP BY ddr.city ");
        List<Record> list = Db.find(sql.toString());
        Map<String, Record> map = new HashMap<>();
        for (Record r : list) {
            map.put(r.getStr("city"), r);
        }
        return map;
    }


    /**
     * 当月活跃人数
     */
    public Map<Integer,Long> getActiveCount(String date){
        Map<String,String> params = getQueryActiveDate(date);
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(DISTINCT(t.user_id)) as activeUserCount,t.CityCode FROM ");
        sql.append("((SELECT userid AS user_id,o.CityCode FROM sales_promotion sp INNER JOIN organization o ON sp.org_id = o.id WHERE sp.moduleId!=1 and sp.return_code='0' and sp.time > '" + params.get(START_TIME) + "' AND sp.time < '" + params.get(END_TIME) + "')");
        sql.append(" UNION ALL ");
        sql.append(" (SELECT user_id,o.CityCode FROM service_reservation sr INNER JOIN organization o ON sr.org_id = o.id WHERE sr.install_address is not null and sr.is_deleted=0 and sr.create_date > '" + params.get(START_TIME) + "' AND sr.create_date < '" +  params.get(END_TIME) + "')");
        sql.append(" UNION ALL ");
        sql.append(" (SELECT user_id,o.CityCode FROM broadband_open_order boo INNER JOIN organization o ON boo.org_id = o.id WHERE boo.order_status='10' and boo.is_deleted=0 and boo.created_time > '" + params.get(START_TIME) + " ' AND boo.created_time < '" + params.get(END_TIME) + "')");
        sql.append(") as t GROUP BY t.CityCode");
        List<Record> bbCounts = Db.find(sql.toString());
        Map<Integer,Long> map = new HashMap<>();
        for(Record r:bbCounts){
            map.put(r.getInt("CityCode"),r.getLong("activeUserCount"));
        }
        return map;
    }

    /**
     * 获取日报数据
     * @return
     */
    public List<Record> getDailyList(String date){
        String sql = "SELECT city,register_channel_count,active_channel_count,register_member_count,active_member_count,postage_count,traffic_count,marketing_activities_count,service_reservation_count,broadband_count,open_count,register_realname_count,good_business_count,recharge_count,cast(recharge_amount/100 as decimal(18,2)) AS rechargeAmount,count_date FROM dzg_daily_report where 1=1 ";
        sql += " AND count_date='" + date + "'";
        sql += " UNION ALL ";
        sql += " SELECT '合计' AS city,sum(register_channel_count) as register_channel_count,sum(active_channel_count) as active_channel_count,sum(register_member_count) as register_member_count,sum(active_member_count) as active_member_count,sum(postage_count) as postage_count,sum(traffic_count) as traffic_count,";
        sql += "sum(marketing_activities_count) as marketing_activities_count,sum(service_reservation_count) as service_reservation_count,sum(broadband_count) as broadband_count,sum(open_count) as open_count,sum(register_realname_count) as register_realname_count,sum(good_business_count) as good_business_count,sum(recharge_count) as recharge_count,sum(cast(recharge_amount/100 as decimal(18,2))) as recharge_amount,count_date from dzg_daily_report";
        sql += "  WHERE 1=1 AND count_date='" + date + "'";
        List<Record> list = Db.find(sql);
        return  list;
    }

    private Map<String,String> getQueryActiveDate(String queryDate){

        Date now = new Date();
        String today = DateKit.toStr(new Date());
        Date date = DateKit.toDate(queryDate,"yyyy-MM");
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.MONTH, 0);
        Date theDate = calendar.getTime();

        Map<String,String> map = new HashMap<>();
        //上个月第一天
        GregorianCalendar gcLast = (GregorianCalendar) Calendar.getInstance();
        gcLast.setTime(theDate);
        gcLast.set(Calendar.DAY_OF_MONTH, 1);
        String day_first = df.format(gcLast.getTime());
        StringBuffer sb = new StringBuffer().append(day_first).append(" 00:00:00");
        day_first = sb.toString();
        map.put(START_TIME, day_first);
        if(today.contains(queryDate)){//获取本月当前一天时间
            calendar.setTime(now);
            calendar.add(Calendar.DATE, -1);
            date = calendar.getTime();
            SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd");
            String lastDay = dateFormater.format(date)+" 23:59:59";
            map.put(END_TIME, lastDay);
        }else{//获取前一个月第一天和最后一天
            calendar.add(Calendar.MONTH, 1);    //加一个月
            calendar.set(Calendar.DATE, 1);        //设置为该月第一天
            calendar.add(Calendar.DATE, -1);    //再减一天即为上个月最后一天
            String lastDay = df.format(calendar.getTime());
            StringBuffer endStr = new StringBuffer().append(lastDay).append(" 23:59:59");
            lastDay = endStr.toString();
            map.put(END_TIME, lastDay);
        }
        return map;
    }

}
